/*This file was used to create all the tables in our database*/

import java.sql.*;
import org.apache.derby.jdbc.ClientDriver;

public class CreateTables
{
	public static void main(String[] args)
	{
		Connection conn = null;
		Statement stmt;
		String qry;
		try
		{
			// Step 1: connect to database server
			Driver d = new ClientDriver();
			String url = "jdbc:derby://localhost:4567/moviedb";
			conn = d.connect(url, null);

			// Step 2: build and execute the query
			//GENRE
			stmt = conn.createStatement();
			qry = "CREATE TABLE GENRE ( "
					+ "genre VARCHAR(12) not null primary key"
					+ ")";			
			stmt.executeUpdate(qry);
			System.out.println("Created table GENRE in given database...");

			//WRITER
			stmt = conn.createStatement();
			qry = "CREATE TABLE WRITER ( "
					+ "WriterId int not null primary key "
					+ "GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), "
					+ "fname VARCHAR(25) not null, "
					+ "lname VARCHAR(25) not null"
					+ ")";			
			stmt.executeUpdate(qry);
			System.out.println("Created table WRITER in given database...");
			
			//DIRECTOR
			stmt = conn.createStatement();
			qry = "CREATE TABLE DIRECTOR ( "
					+ "DirectorId int not null primary key "
					+ "GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), "
					+ "fname VARCHAR(25), "
					+ "lname VARCHAR(25)"
					+ ")";			
			stmt.executeUpdate(qry);
			System.out.println("Created table DIRECTOR in given database...");
			
			//ACTOR
			stmt = conn.createStatement();
			qry = "CREATE TABLE ACTOR ( "
					+ "ActorId int not null primary key "
					+ "GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), "
					+ "fname VARCHAR(25), "
					+ "lname VARCHAR(25)"					
					+ ")";			
			stmt.executeUpdate(qry);
			System.out.println("Created table ACTOR in given database...");
			
			//STUDIO
			stmt = conn.createStatement();
			qry = "CREATE TABLE STUDIO ( "
					+ "StudioId int not null primary key "
					+ "GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), "
					+ "name VARCHAR(25) not null, "
					+ "location VARCHAR(10)"
					+ ")";			
			stmt.executeUpdate(qry);
			System.out.println("Created table STUDIO in given database...");
			
			//MOVIE
			stmt = conn.createStatement();
			qry = "CREATE TABLE MOVIE ( "
					+ "MovieId int not null primary key "
					+ "GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), "
					+ "title VARCHAR(80) not null, "
					+ "mYear int, "
					+ "genre VARCHAR(12), "
					+ "WriterId int, "
					+ "DirectorId int, "
					+ "StudioId int, "
					+ "rating VARCHAR(10), "
					+ "foreign key (Genre) references GENRE on delete no action, "
					+ "foreign key (WriterId) references WRITER on delete no action, "
					+ "foreign key (DirectorId) references DIRECTOR on delete no action, "
					+ "foreign key (StudioId) references STUDIO on delete no action, "
					+ "check (mYear >= 1800) "
					+ ")";			
			stmt.executeUpdate(qry);
			System.out.println("Created table MOVIE in given database...");
			
			//ROLE
			stmt = conn.createStatement();
			qry = "CREATE TABLE ROLE ( "
					+ "RoleId int not null primary key "
					+ "GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), "
					+ "MovieId int not null, "
					+ "ActorId int not null, "
					+ "foreign key (MovieId) references MOVIE on delete no action, "
					+ "foreign key (ActorId) references ACTOR on delete no action "
					+ ")";			
			stmt.executeUpdate(qry);
			System.out.println("Created table ROLE in given database...");
			
		}
		
		catch(SQLException e)
		{
			e.printStackTrace();
		}
		
		finally
		{
			// Step 4: Disconnect from the server
			try
			{
				if(conn != null)
				conn.close();
			}
			catch(SQLException e)
			{
				e.printStackTrace();
			}
		}
	}
}